package com.bcu.cn.onlineexamsystem.studentmanage.mapper;

import com.bcu.cn.onlineexamsystem.studentmanage.controller.ScoreAnalysisDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * 成绩分析数据访问层
 */
@Mapper
public interface ScoreAnalysisMapper {
    
    /**
     * 获取学生的所有考试记录
     */
    @Select("SELECT se.record_id, e.exam_name, c.course_name, se.submit_time as exam_time, " +
            "se.total_score as score, e.total_score as total_score, " +
            "CASE se.status WHEN 0 THEN '未考试' WHEN 1 THEN '考试中' WHEN 2 THEN '已提交' END as status " +
            "FROM student_exams se " +
            "JOIN exams e ON se.exam_id = e.exam_id " +
            "JOIN courses c ON e.course_id = c.course_id " +
            "WHERE se.student_id = #{studentId} AND se.status = 2 " +
            "ORDER BY se.submit_time DESC")
    List<ScoreAnalysisDTO.ExamRecord> getStudentExamRecords(@Param("studentId") String studentId);
    
    /**
     * 获取成绩统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as total_exams, " +
            "AVG(se.total_score) as average_score, " +
            "MAX(se.total_score) as highest_score, " +
            "MIN(se.total_score) as lowest_score, " +
            "SUM(CASE WHEN se.total_score >= e.total_score * 0.6 THEN 1 ELSE 0 END) as pass_count, " +
            "SUM(CASE WHEN se.total_score < e.total_score * 0.6 THEN 1 ELSE 0 END) as fail_count " +
            "FROM student_exams se " +
            "JOIN exams e ON se.exam_id = e.exam_id " +
            "WHERE se.student_id = #{studentId} AND se.status = 2")
    Map<String, Object> getScoreStatistics(@Param("studentId") String studentId);
    
    /**
     * 获取各课程平均成绩
     */
    @Select("SELECT c.course_name, AVG(se.total_score) as average_score, COUNT(*) as exam_count " +
            "FROM student_exams se " +
            "JOIN exams e ON se.exam_id = e.exam_id " +
            "JOIN courses c ON e.course_id = c.course_id " +
            "WHERE se.student_id = #{studentId} AND se.status = 2 " +
            "GROUP BY c.course_id, c.course_name " +
            "ORDER BY average_score DESC")
    List<Map<String, Object>> getCourseAverageScores(@Param("studentId") String studentId);
    
    /**
     * 获取指定课程的成绩详情
     */
    @Select("SELECT se.total_score as score, e.exam_name, e.total_score as total_score " +
            "FROM student_exams se " +
            "JOIN exams e ON se.exam_id = e.exam_id " +
            "JOIN courses c ON e.course_id = c.course_id " +
            "WHERE se.student_id = #{studentId} AND c.course_id = #{courseId} AND se.status = 2 " +
            "ORDER BY se.submit_time")
    List<Map<String, Object>> getCourseScoreDetails(@Param("studentId") String studentId, @Param("courseId") Long courseId);
    
    /**
     * 获取成绩趋势数据
     */
    @Select("SELECT DATE_FORMAT(se.submit_time, '%Y-%m-%d') as exam_date, " +
            "se.total_score as score, e.exam_name " +
            "FROM student_exams se " +
            "JOIN exams e ON se.exam_id = e.exam_id " +
            "WHERE se.student_id = #{studentId} AND se.status = 2 " +
            "ORDER BY se.submit_time")
    List<Map<String, Object>> getScoreTrend(@Param("studentId") String studentId);
    
    /**
     * 获取成绩分布数据
     */
    @Select("SELECT " +
            "CASE " +
            "  WHEN se.total_score >= 90 THEN '90-100' " +
            "  WHEN se.total_score >= 80 THEN '80-89' " +
            "  WHEN se.total_score >= 70 THEN '70-79' " +
            "  WHEN se.total_score >= 60 THEN '60-69' " +
            "  ELSE '0-59' " +
            "END as score_range, " +
            "COUNT(*) as count " +
            "FROM student_exams se " +
            "WHERE se.student_id = #{studentId} AND se.status = 2 " +
            "GROUP BY score_range " +
            "ORDER BY score_range DESC")
    List<Map<String, Object>> getScoreDistribution(@Param("studentId") String studentId);
    
    /**
     * 获取最近考试记录
     */
    @Select("SELECT se.record_id, e.exam_name, c.course_name, se.total_score as score, " +
            "e.total_score as total_score, se.submit_time " +
            "FROM student_exams se " +
            "JOIN exams e ON se.exam_id = e.exam_id " +
            "JOIN courses c ON e.course_id = c.course_id " +
            "WHERE se.student_id = #{studentId} AND se.status = 2 " +
            "ORDER BY se.submit_time DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getRecentExamRecords(@Param("studentId") String studentId, @Param("limit") Integer limit);
    
    /**
     * 获取班级排名信息
     */
    @Select("SELECT COUNT(*) + 1 as rank " +
            "FROM student_exams se1 " +
            "JOIN exams e1 ON se1.exam_id = e1.exam_id " +
            "JOIN student_exams se2 ON se1.exam_id = se2.exam_id " +
            "JOIN users u ON se2.student_id = u.user_id " +
            "WHERE se1.student_id = #{studentId} " +
            "AND se1.exam_id = #{examId} " +
            "AND se1.status = 2 " +
            "AND se2.status = 2 " +
            "AND u.class_id = (SELECT class_id FROM users WHERE user_id = #{studentId}) " +
            "AND se2.total_score > se1.total_score")
    Long getClassRank(@Param("studentId") String studentId, @Param("examId") Long examId);
    
    /**
     * 获取班级平均分
     */
    @Select("SELECT AVG(se.total_score) as class_average " +
            "FROM student_exams se " +
            "JOIN users u ON se.student_id = u.user_id " +
            "WHERE se.exam_id = #{examId} " +
            "AND se.status = 2 " +
            "AND u.class_id = (SELECT class_id FROM users WHERE user_id = #{studentId})")
    BigDecimal getClassAverage(@Param("studentId") String studentId, @Param("examId") Long examId);
    
    /**
     * 根据课程名称获取课程ID
     */
    @Select("SELECT course_id FROM courses WHERE course_name = #{courseName} LIMIT 1")
    Long getCourseIdByName(@Param("courseName") String courseName);
} 